package com.general.campus.jdbc;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * @ClassName H2JdbcDataSourceServlet
 * @Desc: h2 内存数据库的servlet JNDI 实现
 * @Author wangxuan
 * @Date: 2021/6/17 15:02
 * @Verson: 1.0
 **/
public class H2JdbcDataSourceServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse response) throws ServletException, IOException {

        PrintWriter writer = response.getWriter();
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:comp/env");
            DataSource ds = (DataSource) envContext.lookup("h2/MyDB");
            Connection conn = ds.getConnection();
            writer.println("Get h2 connection success!"+conn);

            Statement statement = conn.createStatement();

            String createTableSql = "DROP TABLE IF EXISTS user;"
                    + "CREATE TABLE user"
                    + "("
                    + "  id BIGINT(20) NOT NULL COMMENT '主键ID',"
                    + "  name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',"
                    + "  age INT(11) NULL DEFAULT NULL COMMENT '年龄',"
                    + "  email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',"
                    + "  PRIMARY KEY (id)"
                    + ");";

            String insertTableSql = "INSERT INTO user (id, name, age, email) VALUES"
                    + "  (1, 'Eli', 18, 'Eli@example.com'),"
                    + "  (2, 'Jack', 10, 'Jack@example.com'),"
                    + "  (3, 'Tom', 28, 'Tom@example.com'),"
                    + "  (4, 'Sandy', 21, 'Sandy@example.com'),"
                    + "  (5, 'Billie', 24, 'Billie@example.com');";
            // todo 不知道这里为什么返回的是false 但是最后查询的时候还是成功了？
            boolean create  = statement.execute(createTableSql);

//            writer.println(create ? "h2 create table user success!": "h2 create table user failed!,:|");
//            if(!create){
//                return;
//            }

            // todo 同上？
            boolean insert = statement.execute(insertTableSql);
//            writer.println(insert ? "h2 insert data into table user success!":
//                    "h2 insert data into table user failed!,:|");
//            if(!insert){
//                return;
//            }

            String sql = "select id, name, age, email from user";
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                writer.println(String.format("id: %d, name: %s, age: %d, email: %s",
                        rs.getInt("id"), rs.getString("name"),
                        rs.getInt("age"), rs.getString("email")));

            }
        } catch (NamingException ex) {
            System.err.println(ex);
            writer.write("Get h2 connection NamingException, e ["+ex+"]");
        } catch (SQLException ex) {
            System.err.println(ex);
            writer.write("Get h2 connection SQLException, e ["+ex+"]");
        }
    }
}
